﻿Imports CrystalDecisions.CrystalReports.Engine
Imports MySql.Data

Public Class SalesmanReports

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Var As New Variables
        Dim DataTable As New DataTable
        Dim RegionalManager As New DataTable
        Dim DistrictManager As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("delete from tbl_TempReport1 where acc_name = '" & Label1.Text & "'", Var.Conn)
            Var.Comm.ExecuteNonQuery()

            Var.Comm = New MySqlClient.MySqlCommand("delete from tbl_TempReport2 where name = '" & Label1.Text & "'", Var.Conn)
            Var.Comm.ExecuteNonQuery()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)

            Var.Adapter.Fill(DataTable)

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id = '" & DataTable.Rows(0)("acc_id_up") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)

            Var.Adapter.Fill(DistrictManager)

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id = '" & DistrictManager.Rows(0)("acc_id_up") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)

            Var.Adapter.Fill(RegionalManager)



            Var.Conn.Close()

            Label1.Text = DataTable.Rows(0)("acc_name")
            Label2.Text = DataTable.Rows(0)("acc_num")
            Label3.Text = DataTable.Rows(0)("acc_type")
            Label4.Text = RegionalManager(0)("acc_name")
            Label5.Text = DistrictManager(0)("acc_name")

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim DataTable2 As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("SELECT dbo.tbl_Accounts.acc_id, dbo.tbl_Accounts.acc_num, dbo.tbl_Accounts.acc_name, dbo.tbl_Sales.sales_amount, dbo.tbl_Sales.sales_date FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id WHERE dbo.tbl_Accounts.acc_id = '" & DataTable.Rows(0)("acc_id") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable2)

            DataGridView1.DataSource = DataTable2

            Var.Conn.Close()
        Catch ex As Exception

        End Try

        Dim total As Integer = 0

        For x = 0 To DataTable2.Rows.Count - 1
            total += DataTable2.Rows(x)("sales_amount")
        Next


        RadLabel7.Text = total.ToString()

    End Sub

    Private Sub SalesmanReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub RadButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton1.Click


        Dim Var As New Variables
        Dim DataTable As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)

            Var.Adapter.Fill(DataTable)

            Var.Conn.Close()

        Catch ex As Exception

        End Try

        Dim DataTable2 As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("SELECT dbo.tbl_Accounts.acc_id, dbo.tbl_Accounts.acc_num, dbo.tbl_Accounts.acc_name, dbo.tbl_Sales.sales_amount, dbo.tbl_Sales.sales_date FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id WHERE dbo.tbl_Accounts.acc_id = '" & DataTable.Rows(0)("acc_id") & "' and dbo.tbl_Sales.sales_date between '" & RadDateTimePicker1.Value & "' and '" & RadDateTimePicker2.Value & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable2)

            DataGridView1.DataSource = DataTable2

            Var.Conn.Close()
        Catch ex As Exception

        End Try

        Dim total As Integer = 0

        For x = 0 To DataTable2.Rows.Count - 1
            total += DataTable2.Rows(x)("sales_amount")
        Next

        RadLabel7.Text = total.ToString()
    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton2.Click
        ''SalesReport1.Show()
        'Try
        '    Dim ds As New DataSet()
        '    Dim t As DataTable = ds.Tables.Add("sales")
        '    t.Columns.Add("acc_num", Type.[GetType]("System.String"))
        '    t.Columns.Add("acc_name", Type.[GetType]("System.String"))
        '    t.Columns.Add("sales_amount", Type.[GetType]("System.String"))
        '    t.Columns.Add("sales_date", Type.[GetType]("System.String"))


        '    Dim r As DataRow

        '    Dim acc_num As String = String.Empty
        '    Dim acc_name As String = String.Empty
        '    Dim sales_amount As String = String.Empty
        '    Dim sales_date As String = String.Empty


        '    For i As Integer = 0 To DataGridView1.RowCount - 1
        '        acc_num = DataGridView1.Rows(i).Cells("acc_num").Value.ToString()
        '        acc_name = DataGridView1.Rows(i).Cells("acc_name").Value.ToString()
        '        sales_amount = DataGridView1.Rows(i).Cells("sales_amount").Value.ToString()
        '        sales_date = DataGridView1.Rows(i).Cells("sales_date").Value.ToString()

        '        r = t.NewRow()
        '        r("acc_num") = acc_num
        '        r("acc_name") = acc_name
        '        r("sales_amount") = sales_amount
        '        r("sales_date") = sales_date

        '        t.Rows.Add(r)

        '        Dim objRpt As New ReportDocument
        '        objRpt.Database.Tables(0).SetDataSource(t)
        '        SalesReport1.CrystalReportViewer1.ReportSource = objRpt
        '        SalesReport1.CrystalReportViewer1.Dock = DockStyle.Fill
        '        SalesReport1.CrystalReportViewer1.Refresh()
        '        SalesReport1.CrystalReportViewer1.Show()
        '    Next
        'Catch ex As Exception

        '    MsgBox(ex.Message)
        'End Try

        For x = 0 To DataGridView1.Rows.Count - 1
            Dim Var As New Variables

            Try
                Var.Conn.Open()


                Var.Comm = New MySqlClient.MySqlCommand("insert into tbl_TempReport1(acc_num, acc_name, sales_amount, sales_date,name,cell,position,rsm,sm) values('" & DataGridView1.Rows(x).Cells("acc_num").Value & "','" & DataGridView1.Rows(x).Cells("acc_name").Value & "','" & DataGridView1.Rows(x).Cells("sales_amount").Value & "','" & DataGridView1.Rows(x).Cells("sales_date").Value & "','" & Label1.Text & "','" & Label2.Text & "','" & Label3.Text & "','" & Label4.Text & "','" & Label5.Text & "')", Var.Conn)
                Var.Comm.ExecuteNonQuery()

                'Var.Comm = New MySqlClient.MySqlCommand("insert into tbl_TempReport2(name,cellnumber,position,rsm,sm) values('" & Label1.Text & "','" & Label2.Text & "','" & Label3.Text & "','" & Label4.Text & "','" & Label5.Text & "')", Var.Conn)
                'Var.Comm.ExecuteNonQuery()

                Var.Conn.Close()
            Catch ex As Exception

            End Try
        Next

        SalesReport1.Show()

    End Sub
End Class
